//201902104030_何琛选
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Student;
import cn.edu.sdjzu.xg.bysj.service.TeacherService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class StudentDao {
    private static StudentDao studentDao = null;
    //将唯一构造器私有，阻止本类外生成本类的对象
    private StudentDao() {
    }
    //返回本类唯一的对象
    public static StudentDao getInstance() {
        // 静态变量StudentDao为空时，才创建StudentDao对象。
        // 第二次调用本方法StudentDao就不空了，不再创建对象。
        if (studentDao == null){
            studentDao = new StudentDao();
        }
        return studentDao;
    }
    public int count(Connection connection) throws SQLException{
        return CommonDao.getInstance().count(connection,"student");
    }

    public Collection<Student> findAll(
            Connection connection,
            List<Condition> conditionList_json_str,
            Pagination pagination)
            throws SQLException{
        //创建Teacher类型集合类对象,HashSet不保证结果中元素的顺序
        Collection<Student> desiredStudents = new HashSet<Student>();

        int totalNum = StudentDao.getInstance().count(connection);
        //创建查询教师的主语句
        StringBuilder student_sql = new StringBuilder("select * from student");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList_json_str != null){
            String whereClause = Condition.toWhereClause(conditionList_json_str);
            student_sql.append(whereClause);
        }
        //如果参数不为空，为SQL语句添加LIMIT子句
        if (pagination != null){
            student_sql.append(pagination.toLimitClause(totalNum) + " ");
        }
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt_select_all= connection.prepareStatement(student_sql.toString());
        //遍历结果集，根据元组创建对象，并添加到集合类对象中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            String name = resultSet_select_all.getString("name");
            String no = resultSet_select_all.getString("no");
            String remarks = resultSet_select_all.getString("remarks");

            Student desiredStudent =
                    new Student(id,name,no,remarks,
                            TeacherService.getInstance().
                                    find(resultSet_select_all.getInt("id")));
            desiredStudents.add(desiredStudent);
        }
        JdbcHelper.close(resultSet_select_all, pstmt_select_all, connection);
        return desiredStudents;

    }

    public Student find(Integer id,Connection connection) throws SQLException {
        Student desiredStudent = null;

        //创建sql语句
        String findStudent_sql = "select * from student where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(findStudent_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()){
            int student_id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");

            desiredStudent = new Student(student_id,name,no,remarks,
                    TeacherService.getInstance().find(resultSet.getInt("id"))
                    );
        }
        //关闭连接
        JdbcHelper.close(resultSet,pstmt,connection);
        //返回school对象
        return desiredStudent;
    }

    public boolean update(Student student,Connection connection) throws SQLException{
        String updateStudent_sql = "UPDATE student " +
                "SET name = ?,no = ?,remarks=?,user_id = ?,teacher_id " +
                "WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(updateStudent_sql);
        preparedStatement.setString(1,student.getName());
        preparedStatement.setString(2,student.getNo());
        preparedStatement.setString(3,student.getRemarks());
        preparedStatement.setInt(4,student.getSupervisor().getId());
        preparedStatement.setInt(5,student.getId());

        //修改teacher 并判断是否修改成功
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);

        return affectedRowNum>0;
    }

    public boolean add(Student student, Connection connection) throws SQLException{
        PreparedStatement preparedStatement = connection.prepareStatement
                ("INSERT INTO student" +
                                "(id,name ,no,remarks,teacher_id)" +
                                "VALUE (?,?,?,?,?)",
                        //要求返回主键集合
                        Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setInt(1,student.getId());
        preparedStatement.setString(2,student.getName());
        preparedStatement.setString(3,student.getNo());
        preparedStatement.setString(4,student.getRemarks());
        preparedStatement.setInt(5,student.getSupervisor().getId());
        //执行预编译语句，用其返回值、影响的行数为赋值affectedRowNum
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return affectedRowNum>0;
    }

    public boolean delete(Student student,Connection connection) throws SQLException{
        String deleteStudent_sql = "delete from student where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteStudent_sql);
        //为预编译参数赋值
        pstmt.setInt(1,student.getId());
        JdbcHelper.close(null,connection);
        //根据id删除学院 并判断是否删除成功
        return pstmt.executeUpdate()>0;
    }
}
